﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using TpAgentCore.Properties;
using TpBaseObjects.Base;
using TpLogger;
using TpTools;
using TpExtensions;

namespace TpAgentCore.Base {

    public class Agent : AbstractExcelTools {

        #region Members

        private Logger log;
        private delegate int delegateFreeRowNumber(string bereich, Worksheet sheet, Logger log);
        public enum CallNumber {
            All = 0,
            Aepü = 1,
            UpdateJobevidenz,
            UpdateLeistungen,
            UpdateJobEvidenzInternBulk,
            UpdateLeistungenInternBulk,
            Monatsabschluss,
            KorrekturNnle
        }

        #endregion

        #region Constructor

        public Agent() {
            log = new Logger();
            Settings.Default.Reset();
            Settings.Default.Reload();
            log.MessageAdded += new Logger.MessageAddedEventHandler(OutputToScreen);
            log.Add("Start Agent (" + System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() + ")");
        }

        #endregion

        #region Overrides

        public override void SetTpUser() {
            throw new NotImplementedException();
        }

        #endregion

        #region Methods

        private void AddHyperlink(Range cellRange, string hyperLink) {
            if(hyperLink != "") {
                object textToDisplay;
                if(cellRange.Value != null) {
                    textToDisplay = cellRange.Value;
                } else {
                    textToDisplay = "HyperLinkTo";
                }
                cellRange.Hyperlinks.Add(Anchor: cellRange, Address: hyperLink, TextToDisplay: textToDisplay);
            }
        }

        public void ChangeView(Worksheet sh, DateTime from, DateTime to) {
            FinalJobOrderRows(sh);
            FinalJobSetFontAndFormulas(sh, from, to);
        }

        public void ChangeView(Worksheet sh, DateTime from, DateTime to, Extensions.tpContent contentType, Extensions.tpSorting sortingType, Extensions.tpSortOrder sortOrder, Extensions.tpShow showType) {
            if(showType != Extensions.tpShow.List) {
                FinalJobOrderRows(sh, sortingType, sortOrder);
                FinalJobSetFontAndFormulas(sh, from, to, contentType);
            }
        }

        private void ClearRow(AeRow source) {

            #region Projekt

            source.ProjektName = "";
            source.ProjektBezeichnung = "";

            #endregion

            #region Adresse

            source.Plz = "";
            source.Ort = "";
            source.Straße = "";

            #endregion

            #region Erlös

            source.Erlösminderung = 0;
            source.Forderungsausfall = 0;
            source.RabattGesamt = 0;
            source.RabattLeistungenGesamt = 0;
            source.RabattProdukteGesamt = 0;
            source.Schadenfall = 0;
            source.SummeHandelsware = 0;
            source.SummeMontage = 0;
            source.SummeProduktion = 0;

            #endregion

            #region Kosten

            source.KostenAdministration = 0;
            source.KostenHandelsware = 0;
            source.KostenMontage = 0;
            source.KostenProduktion = 0;

            #endregion

            #region Zusatzkosten

            source.ZkBaustellenInfrastrukturFix = 0;
            source.ZkBaustellenInfrastrukturProzent = 0;
            source.ZkErfüllungsgarantie = 0;
            source.ZkHaftrücklass = 0;
            source.ZkProvisionDB = 0;
            source.ZkProvisionErlösFix = 0;
            source.ZkProvisionErlösProzent = 0;
            source.ZkSkonto = 0;

            #endregion

            #region Zeitaufwand

            source.ZeitaufwandAdministration = 0;
            source.ZeitaufwandMontage = 0;
            source.ZeitaufwandProduktion = 0;

            #endregion


        }

        private void CopyInternDetail(Worksheet sourceSheet, Worksheet targetSheet, ProjectRow row, int freeRowNumber) {
            CopyInternDetail(sourceSheet, targetSheet, row, freeRowNumber, false);
        }

        private void CopyInternDetail(Worksheet sourceSheet, Worksheet targetSheet, ProjectRow row, int freeRowNumber, bool clearCells) {
            Range targetRow = targetSheet.Rows[freeRowNumber];
            Range sourceRow = row.MyRow;
            SetFormula(row.RowNumber, sourceSheet, row, false);
            sourceSheet.Activate();
            sourceRow.Select();
            sourceRow.Copy();
            targetSheet.Activate();
            targetRow.Select();
            targetRow.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
            targetRow.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
            ProjectRow tpRow = new ProjectRow(targetRow);
            if(clearCells) {
                tpRow.ClearTpCells(log);
            }
            //targetRow.Font.Color = sourceRow.Font.Color;

        }

        private void ClearInternRow(bool clearContent, ProjectRow row, string fileSuffix) {
            if(clearContent) {
                Range sourceRow = row.MyRow;
                //sourceRow.Delete(Shift:XlDeleteShiftDirection.xlShiftUp);
                sourceRow.ClearContents();
                sourceRow.ClearComments();
                sourceRow.Font.Bold = false;
            } else {

                row.SetExported(fileSuffix.Substring(0, 1).ToUpper() + fileSuffix.Substring(1).ToLower());
            }
        }

        private bool CopyLeistungenInternRow(Worksheet sourceSheet, ProjectRow row, string userName, string fileSuffix, bool useJobJahr, string sheetNameSuffix, bool clearContent, delegateFreeRowNumber myFreeRowNumber) {
            return CopyLeistungenInternRow(sourceSheet, row, userName, fileSuffix, useJobJahr, sheetNameSuffix, clearContent, myFreeRowNumber, false);
        }

        private bool CopyLeistungenInternRow(Worksheet sourceSheet, ProjectRow row, string userName, string fileSuffix, bool useJobJahr, string sheetNameSuffix, bool clearContent, delegateFreeRowNumber myFreeRowNumber, bool clearCells) {

            try {

                string myPath = MaPath;
                if(row.JobDatum.Month != DateTime.Today.AddDays(-1).Month) {
                    myPath = myPath.Replace(DateTime.Today.AddDays(-1).Year.ToString().Right(2), row.JobDatum.Year.ToString().Right(2));
                }
                string targetBookName = userName + fileSuffix + (useJobJahr ? row.JobDatum.Year.ToString().Right(2) : "") + Settings.Default.LeExportFileExtension;
                string targetFileName = Path.Combine(myPath, targetBookName);

                ProjectSheet source = new ProjectSheet(sourceSheet);
                Workbook targetBook = null;
                if(WorkBookIsLoaded(targetBookName)) {
                    targetBook = exApp.Workbooks[targetBookName];
                } else {
                    if(File.Exists(targetFileName) & !Tools.FileIsInUse(targetFileName)) {
                        targetBook = exApp.Workbooks.Open(targetFileName);
                    } else {
                        log.Add(String.Format(Resources.FileIsInUseOrNotExists, targetFileName));
                        Range r1 = row.MyRow;
                        r1.Select();
                        r1.Copy();
                        if(!CopyRowToLesu(row, userName)) {
                            row.MarkAsFailed();
                        }
                        return false;
                    }
                }
                if(targetBook != null) {
                    Worksheet targetSheet = null;
                    string targetSheetName = row.JobDatum.Month.ToString("00") + sheetNameSuffix;

                    targetSheetName = GetTargetSheetName(targetSheetName, targetBook.Name, fileSuffix);
                    targetSheet = targetBook.Sheets[targetSheetName];

                    if(targetSheet != null) {
                        if(!row.IsReadyToStorno) {
                            int freeRowNumber = myFreeRowNumber(fileSuffix, targetSheet, log);
                            if(freeRowNumber != 0) {
                                CopyInternDetail(sourceSheet, targetSheet, row, freeRowNumber, clearCells);
                                SetInternRow(fileSuffix, row, userName);
                                ClearInternRow(clearContent, row, fileSuffix);
                            } else {
                                row.MarkAsFailed();
                                return false;
                            }
                        } else {
                            DeleteInternRow(targetSheet, row);
                        }

                        SortInternFile(targetSheet, fileSuffix);

                    }
                } else {
                    log.Add("CopyRow: targetBook (" + targetBookName + ") not found!");
                }
                if(targetBook != null) {
                    targetBook.Save();
                }
            } catch(Exception ex) {
                log.Add("CopyLeistungenRow", ex);
                row.MarkAsFailed();
                return false;
            }
            return true;
        }

        private void CopyJobevidenzRow(Worksheet sourceSheet, Worksheet targetSheet, Range sourceRow) {
            Range r1 = sourceRow.Cells[1, "H"];
            Range r2 = sourceRow.Cells[1, "BA"];
            string jeArea = Tools.Nz(Convert.ToString(r1.Value), "");
            string hyperLink = Tools.Nz(Convert.ToString(r2.Value), "");
            if(jeArea != "Verk" & jeArea != "Kund" & jeArea != "Verl") {
                ProjectRow sourceProjectRow = new ProjectRow(sourceRow);
                sourceProjectRow.MarkAsFailed();
                return;
            }

            int freeRowNumber = GetInFreeRowNumber(targetSheet, jeArea, "");
            Range targetRow = GetNewRow(targetSheet, freeRowNumber);
            sourceSheet.Activate();
            sourceRow.Select();
            sourceRow.Copy();
            targetSheet.Activate();
            targetRow.Select();
            targetRow.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
            targetRow.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
            targetRow.Font.Color = sourceRow.Font.Color;

            SetRowSettings(targetRow, hyperLink);
            ProjectRow newRow = new ProjectRow(targetRow);
            log.Add("Update Jobevidenz: " + newRow.KundenName + newRow.Spalte("D") + ", " + targetSheet.Name + ";" + newRow.Leistung + ";" + newRow.JobDatum.ToString(Extensions.tpDateFormat));
            sourceRow.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            sourceRow.ClearContents();
            sourceRow.ClearComments();
        }

        private bool CopyRowToIn(Worksheet sourceSheet, ProjectRow row, string userName, string bereich, string teilBereich) {
            bool retOK = false;
            string workBookName = userName + "je" + Settings.Default.UserInterneJobsFileExtension;
            string fileName = Path.Combine(MaPath, workBookName);
            Workbook wb = null;
            try {
                if(WorkBookIsLoaded(workBookName)) {
                    wb = exApp.Workbooks[workBookName];
                } else {
                    if(File.Exists(fileName) & !Tools.FileIsInUse(fileName)) {
                        wb = exApp.Workbooks.Open(fileName);
                    } else {
                        log.Add(String.Format(Resources.FileIsInUseOrNotExists, fileName));
                        if(!CopyRowToJesu(row, userName)) {
                            row.MarkAsFailed();
                        }
                        return false;
                    }
                }
                Worksheet targetSheet = null;
                if(wb != null) {
                    targetSheet = wb.Sheets[userName];
                    if(targetSheet != null) {
                        int freeRowNumber = GetInFreeRowNumber(targetSheet, bereich, teilBereich);
                        if(freeRowNumber == 0) {
                            freeRowNumber = GetInFreeRowNumber(targetSheet, bereich, "");
                        }
                        if(freeRowNumber != 0) {
                            Range row1 = targetSheet.Rows[freeRowNumber];
                            Range sourceRow = row.MyRow;

                            sourceRow.Copy();
                            targetSheet.Activate();
                            row1.Select();
                            row1.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                            row1.Font.Bold = true;
                            retOK = true;
                        } else {
                            row.MarkAsFailed();
                        }
                    }

                }
            } catch(Exception ex) {
                log.Add("CopyRowToIn", ex);
                row.MarkAsFailed();
            } finally {
                if(wb != null) {
                    wb.Save();
                    wb.Close(true);
                }
            }
            return retOK;
        }

        private bool CopyRowToJesu(ProjectRow row, string userName) {
            //bool ret = CopyRowToFile(row, userName, "Jesu.xlsx");
            return true; // ret;
        }

        private bool CopyRowToLesu(ProjectRow row, string userName) {
            bool ret = CopyRowToFile(row, userName, "Lesu.xlsx");
            if(ret) {
                row.SetExported("Le");
                row.SetFontBold(false);
            }
            return ret;
        }

        private bool CopyRowToFile(ProjectRow row, string userName, string fileName) {

            string path = @"\\TP-S01\Torprofi\Admi\Ma\";
            bool ret = true;
            bool IsLoaded = false;

            #region Check File exists

            if(!File.Exists(Path.Combine(path, fileName))) {
                log.Add("CopyRowToFile:" + String.Format(Resources.FileIsInUseOrNotExists, fileName));
                return false;
            }

            #endregion

            Workbook wb;
            if(WorkBookIsLoaded(fileName)) {
                IsLoaded = true;
                wb = exApp.Workbooks[fileName];
            } else {
                wb = exApp.Workbooks.Open(Path.Combine(path, fileName));
            }

            if(!Tools.SheetsExists(wb, userName)) {
                log.Add("CopyRowToFile:User(" + userName + ") nicht vorhanden.");
                wb.Close(false);
                return false;
            }

            try {
                Worksheet sh = wb.Sheets[userName];
                int rowNumber = GetFirstFreeRowNumber(3, sh);

                Range r2 = sh.Rows[rowNumber];

                r2.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                r2.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);

            } catch(Exception ex) {
                log.Add("CopyRowToFile", ex);
                ret = false;
            } finally {
                wb.Save();
                if(!IsLoaded) {
                    wb.Close(false);
                }
            }
            return ret;
        }

        private void DeleteEmptyRows(Worksheet sh, ProjectSheet sheet, int startRow, int lastRow, int columnNumber) {
            for(int i = startRow;i < lastRow;i++) {
                Range r1 = sh.Cells[i, columnNumber];
                if(r1.Value == null) {
                    Range row = sh.Rows[i];
                    row.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                    sheet.ZaZeileSummeLeistungen += -1;
                    lastRow += -1;
                }
                if(i >= lastRow) {
                    break;
                }
            }
        }

        public void DeleteInternRow(Worksheet sh, ProjectRow row) {
            int startRow = 3;
            bool weiter = false;
            bool deleted = false;

            while(!weiter & startRow < 5000) {
                Range r1 = sh.Rows[startRow];
                ProjectRow delRow = new ProjectRow(r1);
                Range r2 = sh.Cells[startRow, "B"];
                Range r3 = sh.Cells[startRow, "C"];
                string t1 = Tools.Nz(Convert.ToString(r2.Value), "");
                string t2 = t1 + Tools.Nz(Convert.ToString(r3.Value), "");

                if((((Workbook)sh.Parent).Name.Contains("le")) & t2.ToUpper().Contains("LEISTUNGEN")) {
                    weiter = true;
                }

                if(!weiter) {
                    if(delRow.Equals(row)) {
                        r1.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                        deleted = true;
                        startRow--;
                    }
                }
                startRow++;
            }

            if(!deleted) {
                StringBuilder s1 = new StringBuilder();
                s1.Append("DeleteInternRow : ");
                s1.Append(row.KundenName + " " + row.RowTerm + " ");
                s1.Append(row.JobDatum.ToString(Extensions.tpDateFormat) + " ");
                s1.Append(row.Fahrzeug + " ");
                s1.Append(row.Durchführender1 + " ");
                s1.Append(row.Durchführender2 + " ");
                s1.Append(row.RowLeistung + " ");
                s1.Append("not found!");
                log.Add(s1.ToString());
            }
        }

        private void FinalJobDeleteEmptyRows(Worksheet sh) {
            int startRow = 5;
            bool selectRows = false;
            while(selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if(t1 == "") {
                    Range nextRow = sh.Rows[startRow + 1];
                    string t2 = GetRowDescription(nextRow);
                    nextRow = sh.Rows[startRow + 2];
                    t2 += GetRowDescription(nextRow);
                    if(!t2.Contains("Su")) {
                        row.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                    } else {
                        row.ClearContents();
                        row.ClearComments();
                        startRow++;
                    }
                    continue;
                }
                if(t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                }
                startRow++;
            }

        }

        private void FinalJobOrderRows(Worksheet sh) {
            FinalJobOrderRows(sh, Extensions.tpSorting.Area, Extensions.tpSortOrder.Up);
        }

        private void FinalJobOrderRows(Worksheet sh, Extensions.tpSorting sortingType, Extensions.tpSortOrder sortOrder) {
            int startRow = 5;
            int firstAreaRow = 5;
            bool selectRows = false;
            while(selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if(t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if(t1.Contains("Su")) {
                    string[] sortColumns;

                    if(sortingType == Extensions.tpSorting.Area) {
                        sortColumns = Settings.Default.SortColumnsArea.Split(',');
                    } else if(sortingType == Extensions.tpSorting.Date) {
                        sortColumns = Settings.Default.SortColumnsDate.Split(',');
                    } else {
                        sortColumns = Settings.Default.SortColumnsHour.Split(',');
                    }

                    SortSheetRows(sh, firstAreaRow, startRow - 1, sortColumns, sortOrder);
                    firstAreaRow = startRow + 1;
                }
                startRow++;
            }
        }

        private void FinalJobInsertEmptyRows(Worksheet sh) {
            int startRow = 5;
            int emptyRows = 0;
            sh.Activate();
            bool selectRows = false;
            while(selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if(t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if(t1 == "") {
                    emptyRows += 1;
                }
                if(t1.Contains("Su")) {
                    if(emptyRows < 3) {
                        for(int i = 0;i < (3 - emptyRows);i++) {
                            Range selRow = sh.Rows[startRow + i - 1];
                            selRow.Select();
                            selRow.Copy();
                            selRow.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                            Range targetRow = sh.Rows[startRow + i];
                            targetRow.ClearContents();
                            targetRow.ClearComments();
                        }
                        startRow += (4 - emptyRows);
                        emptyRows = 0;
                    } else {
                        emptyRows = 0;
                        startRow++;
                    }
                } else {
                    startRow++;
                }
            }
        }

        private void FinalJobSetAllAreasToVisible(Worksheet sh) {
            int startRow = 5;
            sh.Activate();
            bool selectRows = false;
            while(selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if(t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                Range r1 = sh.Rows[startRow];
                if(r1.EntireRow.Hidden) {
                    r1.EntireRow.Hidden = false;

                }
                startRow++;
            }
        }

        private void FinalJobSetEmptyAreasToHidden(Worksheet sh) {
            int startRow = 5;
            int firstAreaRow = 5;
            int emptyRows = 0;
            sh.Activate();
            bool selectRows = false;
            while(selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if(t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if(t1 == "") {
                    emptyRows += 1;
                }
                if(t1.Contains("Su")) {
                    if((startRow - firstAreaRow) == emptyRows) {
                        Range r1 = sh.Rows[firstAreaRow.ToString() + ":" + (startRow - 1).ToString()];
                        r1.EntireRow.Hidden = true;
                    }
                    firstAreaRow = startRow + 1;
                    emptyRows = 0;
                }
                startRow++;
            }
        }

        private void FinalJobSetFontAndFormulas(Worksheet sh, DateTime showFrom, DateTime showTo) {
            FinalJobSetFontAndFormulas(sh, showFrom, showTo, Extensions.tpContent.AllJobs);
        }

        private void FinalJobSetFontAndFormulas(Worksheet sh, DateTime showFrom, DateTime showTo, Extensions.tpContent contentType) {
            int startRow = 5;
            int jobRows = 0;
            sh.Activate();
            bool selectRows = false;
            while(selectRows == false & startRow < 5000) {
                Range row = sh.Rows[startRow];
                string t1 = GetRowDescription(row);
                if(t1.ToUpper().Contains("SUMME LEISTUNGEN")) {
                    selectRows = true;
                    continue;
                }
                if(t1.ToUpper().Contains("SU")) {
                    Range r2 = sh.Cells[startRow, "H"];
                    r2.Value = jobRows;
                    jobRows = 0;
                    startRow++;
                    continue;
                }

                ProjectRow myRow = new ProjectRow(row);
                bool jobDateIsReady = (myRow.JobDatum >= showFrom) & (myRow.JobDatum <= showTo);
                bool isProjectRow = myRow.Spalte("AI").ToString() != "";

                row.EntireRow.Hidden = true;
                if(contentType == Extensions.tpContent.AllJobs & jobDateIsReady) {
                    row.EntireRow.Hidden = false;
                } else if(contentType == Extensions.tpContent.InternJobs & jobDateIsReady & !isProjectRow) {
                    row.EntireRow.Hidden = false;
                } else if(contentType == Extensions.tpContent.ProjectJobs & jobDateIsReady & isProjectRow) {
                    row.EntireRow.Hidden = false;
                }

                SetFormula(startRow, sh, myRow, true);
                row.Font.Bold = (row.EntireRow.Hidden ? row.Font.Bold : true);
                jobRows += (row.EntireRow.Hidden ? 0 : 1);
                startRow++;
            }
        }

        public void FinalJobsForInternFiles() {
            foreach(Workbook wb in exApp.Workbooks) {
                if(wb.Name.Contains("je") & wb.Name.Contains(".xlsm")) {
                    Worksheet sh = wb.Sheets[wb.Name.Left(2)];
                    FinalJobSetAllAreasToVisible(sh);
                    FinalJobDeleteEmptyRows(sh);
                    FinalJobOrderRows(sh, Extensions.tpSorting.Date, Extensions.tpSortOrder.Up);
                    FinalJobInsertEmptyRows(sh);
                    FinalJobSetEmptyAreasToHidden(sh);
                    FinalJobSetFontAndFormulas(sh, new DateTime(DateTime.Today.AddYears(-10).Year, 1, 1), DateTime.Today, Extensions.tpContent.AllJobs);
                }
            }


        }

        private int FindCheckRow(object toFind, Range selRange, Range activeCell) {
            Range r1;
            r1 = selRange.Find(toFind, activeCell, SearchDirection: XlSearchDirection.xlNext);
            if(r1 != null) {
                return r1.Row;
            }
            return 0;
        }

        private string GetTargetSheetName(string defaultName, string targetBookName, string fileSuffix) {
            if(fileSuffix == "je") {
                defaultName = "In";
            }
            if(targetBookName.Contains("Fehler")) {
                defaultName = (fileSuffix == "je" ? "Jobevidenzen" : "Leistungen");
            }
            return defaultName;
        }

        private void KorrekturCheckFile(Workbook wb) {
            try {
                foreach(Worksheet sh in wb.Worksheets) {
                    if(sh.Name.IsNumeric(System.Globalization.NumberStyles.Any)) {
                        object toFind = "S";
                        Range r1 = sh.Cells[1, "AY"];
                        Range r2 = sh.Columns[r1.Column];
                        if(FindCheckRow(toFind, r2, r1) > 0) {
                            KorrekturMakeStorno(sh, wb.Name.Left(2));
                        }
                        toFind = "K";
                        if(FindCheckRow(toFind, r2, r1) > 0) {
                            KorrekturMakeChanges(sh, wb.Name.Left(2));
                        }

                    }
                }
            } catch(Exception ex) {
                log.Add("KorrekturCheckFile", ex);
            }
        }

        private void KorrekturMakeStorno(Worksheet sh, string d1) {
            bool weiter = false;
            object toFind = "S";
            Range r1 = sh.Cells[3, "AY"];
            Range r2 = sh.Columns[r1.Column];
            while(!weiter) {
                sh.Activate();
                int rowNumber = FindCheckRow(toFind, r2, r1);
                if(rowNumber == 0) {
                    return;
                }
                Range row = sh.Rows[rowNumber];
                ProjectRow stornoRow = new ProjectRow(row);
                stornoRow.ChangeType = "W";
                if(stornoRow.IsProjectJob) {
                    KorrekturProjectFile(d1, stornoRow, "S");
                } else {
                    log.Add("KorrekturMakeStorno:Zeile ist keine Project-Zeile (" + stornoRow.KundenName + "/" + stornoRow.ProjectName);
                }
                rowNumber = 0;
            }
        }

        private void KorrekturMakeChanges(Worksheet sh, string d1) {
            bool weiter = false;
            object toFind = "K";
            Range r1 = sh.Cells[3, "AY"];
            Range r2 = sh.Columns[r1.Column];
            while(!weiter) {
                sh.Activate();
                int rowNumber = FindCheckRow(toFind, r2, r1);
                if(rowNumber == 0) {
                    return;
                }
                Range row = sh.Rows[rowNumber];
                ProjectRow changeRow = new ProjectRow(row);

                changeRow.ChangeType = "W";
                if(changeRow.IsProjectJob) {
                    KorrekturProjectFile(d1, changeRow, "K");
                } else {
                    log.Add("KorrekturMakeChanges:Zeile ist keine Project-Zeile (" + changeRow.KundenName + "/" + changeRow.ProjectName + "/" + changeRow.Leistung);
                }

            }
        }

        private void KorrekturNnle() {
            log.Add("Start KorrekturNnle");
            try {

                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesNnle);
                foreach(string fileName in myFiles) {
                    if(File.Exists(fileName) & !Tools.FileIsInUse(fileName)) {
                        Workbook wb = exApp.Workbooks.Open(fileName);
                        KorrekturCheckFile(wb);
                        wb.Save();
                        wb.Close();
                    } else {
                        log.Add(String.Format(Resources.FileIsInUseOrNotExists, fileName));
                        continue;
                    }
                }

            } catch(Exception ex) {
                log.Add("KorrekturNnle", ex);
            }
        }

        private void KorrekturProjectFile(string d1, ProjectRow row, string kor) {

            string filePath = Settings.Default.CustomerFilePath + row.ProjectName.Left(2) + @"\" + row.KundenName.Left(1) + @"\";
            string fileName = row.KundenName + row.ProjectName.Substring(0, 4) + ".xlsm";
            if(!File.Exists(Path.Combine(filePath + fileName))) {
                filePath = Settings.Default.SalesLeadPath + row.KundenName.Left(1) + @"\";
            }
            if(!File.Exists(Path.Combine(filePath + fileName))) {
                log.Add("KorrekturProjectFile(" + fileName + ") nicht vorhanden!");
                return;
            }
            Workbook wb;
            if(WorkBookIsLoaded(fileName)) {
                wb = exApp.Workbooks[fileName];
            } else {
                wb = exApp.Workbooks.Open(Path.Combine(filePath + fileName));
            }
            string sheetName = (string)row.Spalte("D");
            if(!sheetName.Contains("p")) {
                log.Add("KorrekturProjectFile:ProjektName nicht korrekt. " + row.KundenName + "/" + (string)row.Spalte("D"));
                wb.Close(false);
                return;
            }
            int lenStr = sheetName.Length;
            int posStr = sheetName.IndexOf("p");
            sheetName = sheetName.Right(lenStr - posStr).ToUpper() + "r";
            if(!Tools.SheetsExists(wb, sheetName)) {
                log.Add("KorrekturProjectFile:Arbeitsblatt (" + sheetName + ")nicht vorhanden. " + row.KundenName + "/" + (string)row.Spalte("D"));
                wb.Close(false);
                return;
            }
            try {
                Worksheet sh = wb.Sheets[sheetName];
                int rowNumber = 301;
                bool weiter = false;
                while(!weiter) {
                    Range selRow = sh.Rows[rowNumber];
                    ProjectRow targetRow = new ProjectRow(selRow);

                    if(targetRow.KundenName == row.KundenName &
                        targetRow.ProjectName == row.ProjectName &
                        targetRow.JobDatum == row.JobDatum &
                        targetRow.JobTime == row.JobTime &
                        targetRow.RowType == row.RowType &
                        targetRow.Fahrzeug == row.Fahrzeug &
                        targetRow.Durchführender1 == row.Durchführender1 &
                        targetRow.Durchführender2 == row.Durchführender2 &
                        targetRow.Leistung == row.Leistung
                        ) {
                        if(row.Durchführender1 != d1) {
                            log.Add("KorrekturProjectFile:Durchführender ist nicht D1");
                            continue;
                        }
                        if(kor.ToUpper() == "K") {

                            targetRow.JobDatum = (DateTime)(row.ChangeDate != new DateTime(1900, 1, 1) ? row.ChangeDate : targetRow.JobDatum);
                            targetRow.JobTime = (row.ChangeBeginTime != 0 ? row.ChangeBeginTime.ToString() : targetRow.JobTime);
                            targetRow.JobEndTime = (row.ChangeEndTime != 0 ? row.ChangeEndTime.ToString() : targetRow.JobEndTime);
                            if(targetRow.IsPauschalTime) {
                                targetRow.PauschalZeit = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : targetRow.PauschalZeit);
                            }
                            targetRow.LeistungProzent = (row.ChangePerformance != 0 ? row.ChangePerformance : targetRow.LeistungProzent);
                            targetRow.Menge = (row.Menge != 0 ? row.Menge : targetRow.Menge);
                            if(row.Durchführender2 != "") {
                                KorrekturMitarbeiterRow(row, row.Durchführender2 + wb.Name.Substring(2) + ".xlsx", row.JobDatum.Month.ToString("00"), kor);
                            }
                            row.JobDatum = (DateTime)(row.ChangeDate != new DateTime(1900, 1, 1) ? row.ChangeDate : row.JobDatum);
                            row.JobTime = (row.ChangeBeginTime != 0 ? row.ChangeBeginTime.ToString() : row.JobTime);
                            row.JobEndTime = (row.ChangeEndTime != 0 ? row.ChangeEndTime.ToString() : row.JobEndTime);
                            row.PauschalZeit = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : row.PauschalZeit);
                            row.LeistungZeitSumme = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : row.LeistungZeitSumme);
                            row.LeistungProzent = (row.ChangePerformance != 0 ? row.ChangePerformance : row.LeistungProzent);
                            row.Menge = (row.Menge != 0 ? row.Menge : row.Menge);
                            log.Add("Row wurde geändert:" + row.KundenName + "/" + row.ProjectName + "/" + row.Leistung);
                            ResetChanges(row);
                            weiter = true;
                        }
                        if(kor.ToUpper() == "S") {
                            targetRow.Durchführender1 = "";
                            targetRow.Durchführender2 = "";
                            targetRow.Fahrzeug = "";
                            targetRow.JobDatum = new DateTime(1900, 1, 1);
                            targetRow.JobTime = "";
                            targetRow.JobEndTime = "";
                            targetRow.SetExported("");
                            if(row.Durchführender2 != "") {
                                KorrekturMitarbeiterRow(row, row.Durchführender2 + "le" + row.JobDatum.Year.ToString().Right(2) + ".xlsx", row.JobDatum.Month.ToString("00"), kor);
                            }
                            log.Add("Row wurde storniert:" + row.KundenName + "/" + row.ProjectName + "/" + row.Leistung);
                            Range delRow = row.MyRow;
                            delRow.Delete(XlDeleteShiftDirection.xlShiftUp);
                            weiter = true;
                        }
                        wb.Save();
                    }
                    rowNumber += 1;
                    if(!weiter & (rowNumber == 10000 | targetRow.RowType == "Db")) {
                        weiter = true;
                    }

                }
            } catch(Exception ex) {
                log.Add("KorrekturProjectFile", ex);
            } finally {
                wb.Close(true);
            }
        }

        private void KorrekturMitarbeiterRow(ProjectRow row, string ma, string monat, string kor) {
            string fileName = Path.Combine(MaPath, ma);
            if(!File.Exists(Path.Combine(fileName))) {
                log.Add("KorrekturMitarbeiterRow:(" + fileName + ") nicht vorhanden!");
                return;
            }
            Workbook wb;
            if(WorkBookIsLoaded(ma)) {
                wb = exApp.Workbooks[ma];
            } else {
                wb = exApp.Workbooks.Open(fileName);
            }
            Worksheet sh = wb.Sheets[monat];
            int rowNumber = 3;
            Range r1;
            ProjectRow targetRow;
            bool weiter = false;
            while(!weiter) {
                r1 = sh.Rows[rowNumber];
                targetRow = new ProjectRow(r1);
                if(targetRow.KundenName.Contains("Summe")) {
                    wb.Save();
                    wb.Close();
                    return;
                }
                try {
                    if(targetRow.KundenName == row.KundenName &
                            targetRow.ProjectName == row.ProjectName &
                            targetRow.JobDatum == row.JobDatum &
                            targetRow.JobTime == row.JobTime &
                            targetRow.RowType == row.RowType &
                            targetRow.Fahrzeug == row.Fahrzeug &
                            targetRow.Durchführender1 == row.Durchführender1 &
                            targetRow.Durchführender2 == row.Durchführender2 &
                            targetRow.Leistung == row.Leistung
                            ) {
                        if(kor == "K") {
                            targetRow.JobDatum = (DateTime)(row.ChangeDate != new DateTime(1900, 1, 1) ? row.ChangeDate : targetRow.JobDatum);
                            targetRow.JobTime = (row.ChangeBeginTime != 0 ? row.ChangeBeginTime.ToString() : targetRow.JobTime);
                            targetRow.JobEndTime = (row.ChangeEndTime != 0 ? row.ChangeEndTime.ToString() : targetRow.JobEndTime);
                            targetRow.PauschalZeit = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : targetRow.PauschalZeit);
                            targetRow.LeistungZeitSumme = (row.ChangeFlatTime != 0 ? row.ChangeFlatTime : targetRow.LeistungZeitSumme);
                            targetRow.LeistungProzent = (row.ChangePerformance != 0 ? row.ChangePerformance : targetRow.LeistungProzent);
                            targetRow.Menge = (row.Menge != 0 ? row.Menge : targetRow.Menge);
                        }
                        if(kor == "S") {
                            r1.Delete(XlDeleteShiftDirection.xlShiftUp);
                        }
                        wb.Save();
                        wb.Close();
                        return;
                    }
                } catch(Exception ex) {
                    log.Add("KorrekturMitarbeiterRow", ex);
                }
                rowNumber += 1;
            }
        }

        private void ResetChanges(ProjectRow row) {
            Range r1 = row.MyRow.Cells[1, "AY"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "AZ"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BA"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BB"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BC"];
            r1.Value = "";
            r1 = row.MyRow.Cells[1, "BD"];
            r1.Value = "";

        }

        private void Monatsabschluss() {
            Workbook wb = null;

            log.Add("Start Monatsabschluss");
            try {
                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesZaSearchString);

                foreach(string file in myFiles) {
                    try {
                        if(File.Exists(file) & !Tools.FileIsInUse(file)) {
                            wb = exApp.Workbooks.Open(file);
                            Worksheet sh = wb.Sheets[DateTime.Today.AddMonths(-1).Month.ToString("00")];
                            ProjectSheet sheet = new ProjectSheet(sh);

                            DeleteEmptyRows(sh, sheet, sheet.ZaZeileSummeArbeitszeit + 1, sheet.ZaZeileSummeLeistungen, 3);

                            string[] sortColumns = new string[2] { "K", "P" };
                            SortSheetRows(sh, sheet.ZaZeileSummeArbeitszeit + 1, sheet.ZaZeileSummeLeistungen - 1, sortColumns);
                        }
                    } catch(Exception ex) {
                        log.Add("Monatsabschluss.Foreach", ex);
                    }
                }
            } catch(Exception ex) {
                log.Add("Monatsabschluss", ex);
            } finally {
                log.Add("Monatsabschluss");
                if(wb != null) {
                    wb.Save();
                    wb.Close(true);
                }
            }
        }

        private void OutputToScreen(string message) {
            Console.WriteLine(message);
        }

        public void Run() {
            Run(CallNumber.All);
        }

        public void Run(CallNumber callFunction) {

            try {
                if(exApp == null) {
                    return;
                }

                log.Add("MaPath : " + MaPath);
                log.Add("Jesu : " + Jesu);
                log.Add("Lesu : " + Lesu);

                SetApplication(exApp, false);

                if(callFunction == CallNumber.All && !DebugMode) {
                    SetBackup(MaPath, false);
                }

                if(callFunction == CallNumber.Aepü | callFunction == CallNumber.All) {
                    UpdateAe();
                }

                if(callFunction == CallNumber.UpdateJobevidenz | callFunction == CallNumber.All) {
                    UpdateJobEvidenz();
                }

                if(callFunction == CallNumber.UpdateJobEvidenzInternBulk | callFunction == CallNumber.All) {
                    UpdateJobEvidenzIntern(null, true);
                }

                if(callFunction == CallNumber.UpdateLeistungen | callFunction == CallNumber.All) {
                    UpdateLeistungen(DateTime.Today.Year);
                }

                if(callFunction == CallNumber.UpdateLeistungenInternBulk | callFunction == CallNumber.All) {
                    UpdateLeistungenIntern(null, true);
                }

                if((callFunction == CallNumber.Monatsabschluss | callFunction == CallNumber.All) & DateTime.Today.Day == Settings.Default.StartDayForMonatsabschluss) {
                    //Monatsabschluss();
                }

                if(callFunction == CallNumber.KorrekturNnle | callFunction == CallNumber.All) {
                    KorrekturNnle();
                }

                if(DateTime.Today.Month < 3) {
                    if(callFunction == CallNumber.UpdateLeistungen | callFunction == CallNumber.All) {
                        //UpdateLeistungen(DateTime.Today.AddYears(-1).Year);
                    }
                }

            } catch(Exception ex) {
                log.Add("Tp.Agent.Run: ", ex);
            } finally {
                SetApplication(exApp, true);
                log.Add("Excel is started: " + (excelIsStarted ? "Yes" : "No"));
                if(!excelIsStarted) {
                    exApp.Quit();
                }
                if(log.HasErrors) {
                    //log.SendByEMail("Error-Log: TpAgent " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat), null);
                }
            }
        }

        private void SetBackup(string sourcePath, bool overwrite) {
            log.Add("Start SetBackup");
            string[] fileTypes = Settings.Default.FileSearchPatterns.Split(',');

            string bupPath = Path.Combine(sourcePath, @"bup\" + DateTime.Today.ToString("yyyyMMdd") + @"\");
            if(!Directory.Exists(bupPath)) {
                Directory.CreateDirectory(bupPath);
            }
            DirectoryInfo source = new DirectoryInfo(sourcePath);
            foreach(string searchString in fileTypes) {
                foreach(FileInfo fi in source.GetFiles(searchString)) {
                    try {
                        fi.CopyTo(Path.Combine(bupPath, fi.Name), overwrite);
                    } catch(Exception ex) {
                        log.Add("SetBackup", ex);
                    }
                }
            }
            log.Add("End SetBackup");
        }

        private void SetFormula(int startRow, Worksheet sh, ProjectRow row, bool setFinalJobs) {
            Range r1 = sh.Cells[startRow, "AV"];
            Range r3 = sh.Cells[startRow, "Q"];
            Range r4 = sh.Cells[startRow, "P"];
            if(r3.Value != null & r4.Value != null) {
                //if (((string)r1.FormulaR1C1).Contains("*")) {
                //    r1.FormulaR1C1 = "=(R" + startRow.ToString() + "C17-R" + startRow.ToString() + "C16)*" + (setFinalJobs ? (row.MyRow.EntireRow.Hidden ? "0" : "24") : "24");
                //}
                if(((string)r1.FormulaR1C1).Contains("*")) {
                    r1.Value = "=(Q" + startRow.ToString() + "-P" + startRow.ToString() + ")*" + (setFinalJobs ? (row.MyRow.EntireRow.Hidden ? "0" : "24") : "24");
                }
            }
        }

        private void SetInternRow(string fileSuffix, ProjectRow row, string userName) {
            Range sourceRow = row.MyRow;
            SetPattern(sourceRow.Cells[1, "K"], (int)XlPattern.xlPatternNone);
            SetPattern(sourceRow.Cells[1, "U"], (int)XlPattern.xlPatternNone);
            log.Add("Update " + (fileSuffix == "je" ? "Jobevidenzen" : "Leistungen") + ": " + row.KundenName + row.Spalte("D") + ", " + userName + ";" + row.Leistung + ";" + row.JobDatum.ToString(Extensions.tpDateFormat));
            if(fileSuffix != "je") {
                sourceRow.Font.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
            } else {
                sourceRow.Font.Bold = false;
            }

        }

        private void SetPattern(Range r1, int xlPattern) {
            r1.Interior.Pattern = xlPattern;
        }

        private void SetRowSettings(Range targetRow, string hyperLink) {
            Range r3 = targetRow.Cells[1, "C"];
            AddHyperlink(r3, hyperLink);
            r3 = targetRow.Cells[1, "AE"];
            r3.Value = "Je";
            r3 = targetRow.Cells[1, "H"];
            r3.Value = "";
        }

        private void SortInternFile(Worksheet targetSheet, string fileSuffix) {
            ProjectSheet sheet = null;
            if(fileSuffix == "je" | fileSuffix == "pl" | fileSuffix == "il") {
                sheet = new ProjectSheet(targetSheet);
                SortSheetRows(targetSheet, 3, sheet.ZeileSummeLeistungen, new string[] { "K", "P" });
            }
            if(fileSuffix == "le") {
                sheet = new ProjectSheet(targetSheet);

                //Alle Leistungen
                int startRow = sheet.ZeileSummeArbeitszeit + 1;
                int lastRow = sheet.ZeileSummeLeistungen;
                SortSheetRows(targetSheet, startRow, lastRow, new string[] { "K", "P" });

            }
        }

        private void UpdateAe() {
            Workbook ae = null;
            Workbook aesu = null;

            try {
                log.Add("Start Update Aepü : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));

                #region Check File exists or is in use

                if(!File.Exists(Settings.Default.DateiAepüSub)) {
                    log.Add("File " + Settings.Default.DateiAepüSub + " nicht gefunden!");
                    return;
                }

                if(Tools.FileIsInUse(Settings.Default.DateiAepü)) {
                    log.Add("File " + Settings.Default.DateiAepü + " ist geöffnet!");
                    return;
                }

                #endregion

                #region File copy
                string path = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\bup\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
                if(!Directory.Exists(path)) {
                    Directory.CreateDirectory(path);
                }
                string copyFile = Path.Combine(path, "Aepü.xlsm");
                if(File.Exists(copyFile)) {
                    File.Delete(copyFile);
                }
                File.Copy(Settings.Default.DateiAepüSub, copyFile);

                #endregion

                #region Set Worbbooks & WorkSheets

                ae = exApp.Workbooks.Open(Settings.Default.DateiAepü);
                aesu = exApp.Workbooks.Open(Settings.Default.DateiAepüSub);

                Worksheet aeAe = ae.Sheets["Ae"];
                Worksheet aePü = ae.Sheets["Pü"];
                Worksheet aesuAe = aesu.Sheets["AeSub"];

                #endregion

                bool save = false;
                int startRow = Settings.Default.StartZeile;
                bool startRowSelect = false;
                while(startRowSelect == false & startRow < 10000) {

                    Range sourceRow = aesuAe.Rows[startRow];
                    AeRow aesuRow = new AeRow(sourceRow);
                    if(aesuRow.ProjektName != null) {
                        if(TpTools.Tools.ExistsContentInColumn(aeAe, Settings.Default.SpalteKundenName, aesuRow.ProjektName)) {
                            Range aeRow = TpTools.Office.GetAeRow(aeAe, aesuRow.ProjektName, aesuRow.ProjektBezeichnung, 3);
                            if(aeRow != null) {
                                AeRow targetRow = new AeRow(aeRow);
                                UpdateAeProjektDetails(aesuRow, targetRow);
                                string logText = String.Format("Update Ae: {0}{1}", aesuRow.ProjektName, aesuRow.ProjektBezeichnung);
                                log.Add(logText);
                                ClearRow(aesuRow);
                                save = true;
                            }
                        }
                        if(TpTools.Tools.ExistsContentInColumn(aePü, Settings.Default.SpalteKundenName, aesuRow.ProjektName)) {
                            Range aeRow = TpTools.Office.GetAeRow(aePü, aesuRow.ProjektName, aesuRow.ProjektBezeichnung, 3);
                            if(aeRow != null) {
                                AeRow targetRow = new AeRow(aeRow);
                                UpdateAeProjektDetails(aesuRow, targetRow);
                                string logText = String.Format("Update Pü: {0}{1}", aesuRow.ProjektName, aesuRow.ProjektBezeichnung);
                                log.Add(logText);
                                ClearRow(aesuRow);
                                save = true;
                            }
                        }
                    } else {
                        startRowSelect = true;
                    }
                    startRow++;
                }
                if(save) {
                    ae.Save();
                }
                log.Add("Ende Update Aepü : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
            } catch(Exception ex) {
                log.Add("UpdateAepü", ex);
            } finally {
                if(ae != null) {
                    ae.Save();
                    ae.Close(true);
                }
                if(aesu != null) {
                    aesu.Save();
                    aesu.Close(true);
                }
            }
        }

        private void UpdateAeProjektDetails(AeRow source, AeRow target) {

            try {

                #region Projekt

                target.AuftragsDatum = source.AuftragsDatum;

                #endregion

                #region Adresse

                target.Plz = source.Plz;
                target.Ort = source.Ort;
                target.Straße = source.Straße;

                #endregion

                #region Erlös

                target.Erlösminderung = source.Erlösminderung;
                target.Forderungsausfall = source.Forderungsausfall;
                target.RabattGesamt = source.RabattGesamt;
                target.RabattLeistungenGesamt = source.RabattLeistungenGesamt;
                target.RabattProdukteGesamt = source.RabattProdukteGesamt;
                target.Schadenfall = source.Schadenfall;
                target.SummeHandelsware = source.SummeHandelsware;
                target.SummeMontage = source.SummeMontage;
                target.SummeProduktion = source.SummeProduktion;

                #endregion

                #region Kosten

                target.KostenAdministration = source.KostenAdministration;
                target.KostenHandelsware = source.KostenHandelsware;
                target.KostenMontage = source.KostenMontage;
                target.KostenProduktion = source.KostenProduktion;

                #endregion

                #region Zusatzkosten

                target.ZkBaustellenInfrastrukturFix = source.ZkBaustellenInfrastrukturFix;
                target.ZkBaustellenInfrastrukturProzent = source.ZkBaustellenInfrastrukturProzent;
                target.ZkErfüllungsgarantie = source.ZkErfüllungsgarantie;
                target.ZkHaftrücklass = source.ZkHaftrücklass;
                target.ZkProvisionDB = source.ZkProvisionDB;
                target.ZkProvisionErlösFix = source.ZkProvisionErlösFix;
                target.ZkProvisionErlösProzent = source.ZkProvisionErlösProzent;
                target.ZkSkonto = source.ZkSkonto;

                #endregion

                #region Zeitaufwand

                target.ZeitaufwandAdministration = source.ZeitaufwandAdministration;
                target.ZeitaufwandMontage = source.ZeitaufwandMontage;
                target.ZeitaufwandProduktion = source.ZeitaufwandProduktion;

                #endregion

            } catch(Exception ex) {
                log.Add("UpdateAePüProjektDetails", ex);
            }
        }

        private void UpdateJobEvidenz() {
            Workbook jesu = null;
            Workbook targetBook = null;
            try {

                #region Check File exists or is in use

                if(!File.Exists(Jesu)) {
                    log.Add("File " + Jesu + " nicht gefunden!");
                    return;
                }

                if(Tools.FileIsInUse(Jesu)) {
                    log.Add("File " + Jesu + " ist geöffnet!");
                    return;
                }

                #endregion

                #region File copy

                if(Settings.Default.UseBackupJesu) {
                    string path = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\bup\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
                    if(!Directory.Exists(path)) {
                        Directory.CreateDirectory(path);
                    }
                    string copyFile = Path.Combine(path, "Jesu.xlsx");
                    if(File.Exists(copyFile)) {
                        File.Delete(copyFile);
                    }
                    File.Copy(Jesu, copyFile, true);
                }

                #endregion

                log.Add("Start Update Jobevidenz : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
                jesu = exApp.Workbooks.Open(Jesu);

                Worksheet targetSheet;
                int freeRowNumber;
                foreach(Worksheet sh in jesu.Sheets) {
                    ProjectSheet sheet = new ProjectSheet(sh, true);
                    string fileName = Path.Combine(MaPath, sheet.SheetName + "je" + Settings.Default.JeExportFileExtension);

                    if(Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                        log.Add("Datei " + fileName + " ist geöffnet oder nicht vorhanden!");
                        continue;
                    }

                    targetBook = exApp.Workbooks.Open(fileName);
                    foreach(ProjectRow exRow in sheet.MyRows) {
                        try {
                            targetSheet = targetBook.Sheets[sh.Name];

                            CopyJobevidenzRow(sh, targetSheet, exRow.MyRow);

                        } catch(Exception ex) {
                            log.Add("UpdateJobevidenz - CopyRow", ex);
                        }

                    }
                    if(targetBook != null) {
                        targetBook.Save();
                        targetBook.Close(true);
                    }
                    targetBook = null;
                }


                log.Add("Ende Update Jobevidenz : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
            } catch(Exception ex) {
                log.Add("UpdateJobevidenz", ex);
            } finally {
                if(targetBook != null) {
                    targetBook.Save();
                    targetBook.Close(true);
                }
                if(jesu != null) {
                    jesu.Save();
                    jesu.Close(true);
                }
            }
        }

        public void UpdateJobEvidenzIntern(string[] paramFiles, bool runBulk) {
            Workbook wb = null;

            try {
                log.Add("Start UpdateJobEvidenzIntern(" + runBulk.ToString() + ")");

                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesInternSearchString);

                if(paramFiles != null) {
                    if(paramFiles.Length != 0) {
                        myFiles = paramFiles;
                    }
                }

                foreach(string file in myFiles) {
                    if(Tools.FileIsInUse(file) | (!File.Exists(file))) {
                        log.Add(String.Format(Resources.FileIsInUseOrNotExists, file));
                        continue;
                    }
                    wb = exApp.Workbooks.Open(file);
                    Worksheet sh = wb.Sheets[Tools.Left(wb.Name, 2)];
                    ProjectSheet sheet = new ProjectSheet(sh, true, true);
                    log.Add("Start " + wb.Name);
                    foreach(ProjectRow row in sheet.MyRows) {

                        if(row.IsReadyForInternalJobevidenz) {
                            if(row.Durchführender2 != "" & row.Durchführender2 != sh.Name) {
                                CopyRowToIn(sh, row, row.Durchführender2, row.KundenName, row.Spalte("D").ToString());
                                log.Add("Update JobevidenzIntern: " + row.KundenName + row.Spalte("D") + ", " + row.Durchführender2 + ";" + row.Leistung + ";" + row.JobDatum.ToString(Extensions.tpDateFormat));
                            }
                            row.SetExported("Je");
                            if((row.Durchführender1 != "" & row.Durchführender1 != Tools.Left(wb.Name, 2)) & row.Durchführender2 == "") {
                                if(CopyRowToIn(sh, row, row.Durchführender1, row.KundenName, row.Spalte("D").ToString())) {
                                    log.Add("Update JobevidenzIntern: " + row.KundenName + row.Spalte("D") + ", " + row.Durchführender1 + ";" + row.Leistung + ";" + row.JobDatum.ToString(Extensions.tpDateFormat));
                                    row.ClearContent();
                                }
                            }
                        }
                    }
                    wb.Save();
                    if(runBulk) {
                        wb.Close();
                    }
                }

            } catch(Exception ex) {
                log.Add("UpdateJobevidenzIntern", ex);
            } finally {
                if(runBulk) {
                    CloseAllWorkbooks(true);
                }
                log.Add("Ende UpdateJobEvidenzIntern : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
            }
        }

        private void UpdateLeistungen(int jahr) {
            Workbook lesu = null;
            Workbook exLe = null;

            try {

                #region Check File exists or is in use

                if(!File.Exists(Lesu)) {
                    log.Add("File " + Lesu + " nicht gefunden!");
                    return;
                }

                if(Tools.FileIsInUse(Lesu)) {
                    log.Add("File " + Lesu + " ist geöffnet!");
                    return;
                }

                #endregion

                #region File copy

                if(Settings.Default.UseBackupLesu) {
                    string path = Settings.Default.BackupPath + DateTime.Today.AddDays(-1).Year.ToString().Right(2) + @"\bup\" + DateTime.Today.ToString("yyyyMMdd") + @"\";
                    if(!Directory.Exists(path)) {
                        Directory.CreateDirectory(path);
                    }
                    string copyFile = Path.Combine(path, "Lesu.xlsx");
                    if(File.Exists(copyFile)) {
                        File.Delete(copyFile);
                    }
                    File.Copy(Lesu, copyFile);
                }

                #endregion


                log.Add("Start Update Leistungen : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
                lesu = exApp.Workbooks.Open(Lesu);


                foreach(Worksheet sh in lesu.Sheets) {
                    ProjectSheet sheet = new ProjectSheet(sh, true);
                    foreach(ProjectRow row in sheet.MyRows) {
                        //string fileName = row.Durchführender1 + "le" + DateTime.Today.Year.ToString().Right(2) + Settings.Default.LeExportFileExtension;
                        if(CopyLeistungenInternRow(sh, row, sh.Name, "le", true, "", false, GetLeFreeRowNumber, true)) {
                            row.ClearContent();
                        }
                    }
                }

                log.Add("Ende Update Leistungen : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
            } catch(Exception ex) {
                log.Add("UpdateLeistungen", ex);
            } finally {
                if(exLe != null) {
                    exLe.Save();
                    exLe.Close(true);
                }
                if(lesu != null) {
                    lesu.Save();
                    lesu.Close(true);
                }
            }
        }

        private void UpdateLeistungenInternDetail(Worksheet sh, ProjectRow row) {
            if(row.IsReadyForExport & row.JobTime != "0" & ((row.Durchführender1 == sh.Name) || sh.Name == "Bc" || sh.Name == "Sw")) {
                string fileName = row.Durchführender1 + "le" + DateTime.Today.Year.ToString().Right(2) + Settings.Default.LeExportFileExtension;
                bool beOk = CopyLeistungenInternRow(sh, row, row.Durchführender1, "le", true, "", false, GetLeFreeRowNumber);

                if(row.Durchführender2 != "") {
                    fileName = row.Durchführender2 + "le" + DateTime.Today.Year.ToString().Right(2) + Settings.Default.LeExportFileExtension;
                    beOk = CopyLeistungenInternRow(sh, row, row.Durchführender2, "le", true, "", false, GetLeFreeRowNumber);
                }
                if(beOk) {
                    row.ClearContent();
                } else {
                    row.MarkAsFailed();
                }
            }
        }

        public void UpdateLeistungenIntern(Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh, true, true);
            log.Add("Start " + wb.Name);
            foreach(ProjectRow row in sheet.MyRows) {
                if(!row.IsReadyForExport) {
                    continue;
                }
                //UpdateLeistungenInternDetail(sh, row);
                Range r1 = sh.Rows[row.RowNumber];
                r1.Select();
                r1.Copy();
                CopyRowToLesu(row, row.Durchführender1);
                if(!String.IsNullOrEmpty(row.Durchführender2)) {
                    r1.Select();
                    r1.Copy();
                    CopyRowToLesu(row, row.Durchführender2);
                }
            }
        }

        public void UpdateLeistungenIntern(string[] paramFiles, bool runBulk) {
            Workbook wb = null;

            log.Add("Start UpdateLeistungenIntern(" + runBulk.ToString() + ")");
            try {

                string[] myFiles = Directory.GetFiles(MaPath, Settings.Default.FilesInternSearchString);

                if(paramFiles != null) {
                    if(paramFiles.Length != 0) {
                        myFiles = paramFiles;
                    }
                }

                foreach(string file in myFiles) {
                    if(Tools.FileIsInUse(file) | (!File.Exists(file))) {
                        log.Add(String.Format(Resources.FileIsInUseOrNotExists, file));
                        continue;
                    }
                    wb = exApp.Workbooks.Open(file);
                    Worksheet sh = wb.Sheets[wb.Name.Left(2)];
                    ProjectSheet sheet = new ProjectSheet(sh, true, true);
                    log.Add("Start " + wb.Name);
                    foreach(ProjectRow row in sheet.MyRows) {
                        if(!CheckRow(sh, row)) {
                            continue;
                        }
                        UpdateLeistungenInternDetail(sh, row);
                    }
                }


            } catch(Exception ex) {
                log.Add("UpdateLeistungenIntern", ex);
            } finally {
                log.Add("Start FinalJobsForInternFiles");
                FinalJobsForInternFiles();
                log.Add("End FinalJobsForInternFiles");
                if(runBulk) {
                    CloseAllWorkbooks(true);
                }
                log.Add("Ende UpdateLeistungenIntern : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat));
            }
        }

        #endregion

        #region Functions

        private bool CheckRow(Worksheet sh, ProjectRow row) {
            string cellValue = Tools.Nz(Convert.ToString(row.Spalte("C")), "");
            if(cellValue == "") {
                Range r1 = sh.Cells[row.RowNumber, "C"];
                r1.Interior.Color = Settings.Default.Gelb;
                return false;
            }
            if(row.Durchführender1 == "") {
                Range r1 = sh.Cells[row.RowNumber, "N"];
                r1.Interior.Color = Settings.Default.Gelb;
                return false;
            }
            if(row.Fahrzeug == "") {
                Range r1 = sh.Cells[row.RowNumber, "M"];
                r1.Interior.Color = Settings.Default.Gelb;
                return false;
            }
            if(row.JobDatum == new DateTime(1900, 1, 1)) {
                Range r1 = sh.Cells[row.RowNumber, "K"];
                r1.Interior.Color = Settings.Default.Gelb;
                return false;
            }
            cellValue = Tools.Nz(Convert.ToString(row.Spalte("U")), "");
            if(cellValue == "") {
                Range r1 = sh.Cells[row.RowNumber, "U"];
                r1.Interior.Color = Settings.Default.Gelb;
                return false;
            }
            if(DateTime.Today.Day > 5) {
                //if((row.JobDatum.Year == DateTime.Today.Year & row.JobDatum.Month < DateTime.Today.Month) | (row.JobDatum.Year < DateTime.Today.Year)) {
                if(row.JobDatum < new DateTime(2011, 12, 1)) {
                    Range r1 = sh.Cells[row.RowNumber, "K"];
                    r1.Interior.Color = Settings.Default.Gelb;
                    return false;
                }
                //}

            }
            if(row.IsProjectJob) {
                return false;
            }
            return true;
        }

        public Range GetNewRow(Worksheet sh, int startRow) {
            Range row = sh.Rows[startRow];
            return row;
        }

        public int GetInFreeRowNumber(Worksheet sh, string bereich, string teilBereich) {
            int retRow = 0;
            int lastFreeRow = 5;
            startRow = 5;
            int firstAreaRow = 5;
            bool selectRows = false;
            while(!selectRows & startRow < 5000) {
                Range row = sh.Rows[startRow];
                ProjectRow targetRow = new ProjectRow(row);
                if(targetRow.KundenName != "") {

                    if(targetRow.KundenName == bereich & targetRow.Spalte("D").ToString().ToUpper() == teilBereich.ToUpper()) {
                        if(targetRow.JobDatum != new DateTime(1900, 1, 1)) {
                            firstAreaRow = startRow + 1;
                        }
                        if(targetRow.JobDatum == new DateTime(1900, 1, 1)) {
                            if((startRow - lastFreeRow) < 2) {
                                Range r1 = sh.Rows[startRow - 1];
                                sh.Activate();
                                r1.Select();
                                r1.Copy();
                                r1.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                                firstAreaRow = startRow;
                                lastFreeRow = startRow;
                            }
                            return lastFreeRow;
                        }
                    } else {
                        if(targetRow.Spalte("B").ToString().ToUpper() == "SU") {
                            firstAreaRow = startRow + 1;
                        }
                    }
                } else {
                    if((startRow - 1) != lastFreeRow) {
                        lastFreeRow = startRow;
                    }
                }
                startRow++;
            }
            return retRow;
        }

        private int GetJeFreeRowNumber(string bereich, Worksheet sh, Logger log) {
            startRow = 3;
            int freeRow = 0;
            try {
                bool selectRows = false;
                while(selectRows == false & startRow < 5000) {
                    Range r1 = sh.Cells[startRow, 2];
                    Range r2 = sh.Cells[startRow, 3];
                    Range r3 = sh.Cells[startRow, "N"];
                    string t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                    string t2 = t1 + Tools.Nz(Convert.ToString(r2.Value), "");

                    if(t2 != "" | r3.Value != null) {

                        if(t2.ToUpper().Contains("LEISTUNGEN")) {
                            freeRow = startRow;
                            selectRows = true;
                            sh.Activate();
                            Range row = sh.Rows[startRow - 1];
                            row.Select();
                            row.Copy();
                            row.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                        }

                    } else {
                        freeRow = startRow;
                        selectRows = true;
                    }
                    startRow++;
                }
            } catch(Exception ex) {
                log.Add("GetJeFreeRowNumber", ex);
            }
            return freeRow;
        }

        private int GetLeFreeRowNumber(string bereich, Worksheet sh, Logger log) {
            startRow = 3;
            int freeRow = 0;
            int lastRow = 0;
            ProjectSheet sheet = new ProjectSheet(sh);
            try {

                bool selectRows = false;
                switch(bereich) {
                    case "Leistungen":
                    case "le":
                        startRow = (sheet.ZeileSummeArbeitszeit == 0 ? 3 : sheet.ZeileSummeArbeitszeit + 1);
                        lastRow = sheet.ZeileSummeLeistungen;
                        break;
                    default:
                        startRow = 3;
                        lastRow = 5;
                        break;
                }

                selectRows = false;
                while(selectRows == false & startRow < 5000) {
                    Range r1 = sh.Cells[startRow, 2];
                    Range r2 = sh.Cells[startRow, 3];
                    Range r3 = sh.Cells[startRow, "N"];
                    string t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                    string t2 = t1 + Tools.Nz(Convert.ToString(r2.Value), "");
                    if((t2 != "" & r3.Value != null) | (startRow == lastRow)) {
                        if(startRow == lastRow) {
                            freeRow = startRow;
                            selectRows = true;
                            sh.Activate();
                            Range row = sh.Rows[startRow - 1];
                            row.Select();
                            row.Copy();
                            row.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                        }
                    } else {
                        freeRow = startRow;
                        selectRows = true;
                    }
                    startRow++;
                }

            } catch(Exception ex) {
                log.Add("GetLeFreeRowNumber", ex);
            }
            return freeRow;
        }

        private string GetRowDescription(Range row) {
            Range r1 = row.Cells[1, 2];
            Range r2 = row.Cells[1, 3];
            Range r3 = row.Cells[1, 4];
            string t1 = ((string)Tools.Nz(Convert.ToString(r1.Value), "") + " " + Tools.Nz(Convert.ToString(r2.Value), "") + " " + Tools.Nz(Convert.ToString(r3.Value), "")).Trim();
            return t1;
        }

        private static int GetFirstFreeRowNumber(int copyRow, Worksheet sh) {
            Range row = sh.Rows[copyRow];
            Range r1 = row.Cells[1, 3];
            Range r2 = row.Cells[1, "N"];
            while(r1.Value != null && r2.Value != null) {
                copyRow++;
                row = sh.Rows[copyRow];
                r1 = row.Cells[1, 3];
                r2 = row.Cells[1, "N"];
            }
            return copyRow;
        }

        #endregion

        #region Destructor


        ~Agent() {
            log.Add("End Agent");
            if(!ExcelIsStarted()) {
                exApp.Quit();
            }
            if(log.HasErrors | log.LogListCount > 2) {
                log.SaveToDisk();
            }
        }

        #endregion

    }
}
